package xcmg.device.service.safetyStock;

import com.github.pagehelper.PageInfo;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import xcmg.device.dao.entity.PriMaterialsDO;
import xcmg.device.dao.entity.safetyStock.ComSafetyStockInfo;
import xcmg.device.dao.entity.safetyStock.MaterialsSafetyStock;
import xcmg.device.dao.entity.safetyStock.MaterialsSalesData;
import xcmg.device.dao.mapper.PriMaterialsMapper;
import xcmg.device.dao.mapper.safetyStock.ComSafetyStockInfoMapper;
import xcmg.device.dao.mapper.safetyStock.MaterialsSafetyStockMapper;
import xcmg.device.dao.mapper.safetyStock.MaterialsSalesDataMapper;
import xcmg.device.infra.ErrorCode;
import xcmg.device.infra.FileUtil;
import xcmg.device.service.vo.FunctionResult;
import xcmg.device.service.vo.safetyStock.SafetyLevelInfo;
import xcmg.device.util.DecimalUtil;
import yb.ecp.fast.infra.infra.PageCommonVO;
import yb.ecp.fast.infra.infra.SearchCommonVO;
import yb.ecp.fast.infra.util.ListUtil;
import yb.ecp.fast.infra.util.PageHelperPlus;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 安全库存相关公式：
 *
 * 预测需求量=当前月*0.1+前12个月平均值*0.9
 * 订货次数=零件在当前月主机厂的采购计划中出现的次数
 * 方案二的订货次数默认取1
 * 周转库存EOQ=预测需求量D/订货次数AL
 * 周转储备库存LTD=交货期*预测需求量D
 * 库存上限=安全库存+周转储备库存+周转库存
 *
 * @author wangyong
 */
@Service
public class MaterialsSafetyStockService {

  @Autowired
  private MaterialsSafetyStockMapper materialsSafetyStockMapper;

  @Autowired
  private SafetyInventoryStrategy safetyInventoryStrategy;

  @Autowired
  private PriMaterialsMapper priMaterialsMapper;

  @Autowired
  private MaterialsSalesDataService materialsSalesDataService;

  @Autowired
  private ComSafetyStockInfoMapper comSafetyStockInfoMapper;

  @Autowired
  private MaterialsSalesDataMapper materialsSalesDataMapper;

  @Autowired
  private HttpServletResponse response;

  @Value("${spring.file.download.path}")
  private String filePath;

  /**
   * excel模板文件名
   */
  private static final String SAFETY_STOCK_TEMPLATE_FILE = "materials_safety_stock_template.xls";


  private static final String SYSTEM_ACTION_CODE = "00000001",SYSTEM_ACTION_NAME = "system";

  /**
   * 导入的交货期的单位，1：天  2：月
   */
  private static final String DELIVERY_SPEND_UNIT_DAY = "1",DELIVERY_SPEND_UNIT_MONTH = "2";

  /**
   * 定时任务每月初更新安全库存数据
   *
   * @param orgId
   * @param materialsNo
   * @param variance
   * @param now
   * @param planCode
   * @throws Exception
   */
  @Transactional(rollbackFor = Exception.class)
  public void saveOrUpdate(String orgId, String materialsNo, BigDecimal variance, Date now,
      String planCode) throws Exception {

    PriMaterialsDO materialsDO =priMaterialsMapper.itemByNo(materialsNo, orgId);
    if (null == materialsDO) {
      return;
    }
    MaterialsSafetyStock materialsSafetyStock = materialsSafetyStockMapper.getByMaterialsNo(orgId, materialsNo);
    SafetyLevelInfo safetyLevelInfo = safetyInventoryStrategy.getSafetyInfo(orgId, materialsNo, now, planCode, materialsDO.getDeliverySpend());

    if (null != materialsSafetyStock) {
      materialsSafetyStock.setMaterialsDes(materialsDO.getMaterialsDes());
      materialsSafetyStock = fillSafetyStockInfo(materialsSafetyStock, safetyLevelInfo, variance);
      materialsSafetyStockMapper.update(materialsSafetyStock);
      return;
    }
    materialsSafetyStock = new MaterialsSafetyStock();
    materialsSafetyStock.setMaterialsDes(materialsDO.getMaterialsDes());
    materialsSafetyStock.setOrgId(orgId);
    materialsSafetyStock.setMaterialsNo(materialsNo);
    save(materialsSafetyStock, safetyLevelInfo, variance);
  }

  /**
   * 填充零件安全库存信息
   *
   * @param materialsSafetyStock
   * @param safetyLevelInfo
   * @param variance
   * @return
   */
  private MaterialsSafetyStock fillSafetyStockInfo(MaterialsSafetyStock materialsSafetyStock, SafetyLevelInfo safetyLevelInfo, BigDecimal variance) {

    //出库频次
    safetyLevelInfo.getOutRate();
    // 安全等级
    safetyLevelInfo.getSafetyLevel();

    //交货期
    BigDecimal deliverySpend = safetyLevelInfo.getDeliverySpend();

    if (null != deliverySpend) {
      //安全系数
      BigDecimal safetyCons = safetyLevelInfo.getSafetyConstant();

      //安全库存
      BigDecimal safetyStock = safetyCons.multiply(deliverySpend)
          .multiply(variance).setScale(0, BigDecimal.ROUND_UP);

      //周转库存EOQ
      BigDecimal eoqStock = safetyLevelInfo.getEoqStock();
      //周转储备库存LTD
      BigDecimal ltdStock = safetyLevelInfo.getLtdStock();
      // 库存上限=安全库存+周转储备库存+周转库存
      BigDecimal maxLimitStock = safetyStock.add(ltdStock).add(eoqStock);

      // 每月重新计算安全库存时，需要清空手动设置的安全库存
      materialsSafetyStock.setAdjustSafetyStock(null);
      materialsSafetyStock.setEoqStock(eoqStock);
      materialsSafetyStock.setLtdStock(ltdStock);
      materialsSafetyStock.setUpperLimitStock(maxLimitStock);
      materialsSafetyStock.setSalesRate(safetyLevelInfo.getOutRate());
      materialsSafetyStock.setSafetyCons(safetyCons);
      materialsSafetyStock.setSafetyLevel(safetyLevelInfo.getSafetyLevel());
      materialsSafetyStock.setDeliverySpend(deliverySpend);
      materialsSafetyStock.setCalSafetyStock(safetyStock);
      materialsSafetyStock.setRemark(StringUtils.EMPTY);
    } else {
      materialsSafetyStock.setRemark("交货期字段为空");
    }
    materialsSafetyStock.setUpdateTime(new Date());
    materialsSafetyStock.setUpdateId(SYSTEM_ACTION_CODE);
    materialsSafetyStock.setUpdateName(SYSTEM_ACTION_NAME);
    return materialsSafetyStock;
  }


  /**
   * 更新
   *
   * @param
   * @return
   */
  public ErrorCode update(List<MaterialsSafetyStock> list, String userId, String userName) {
      if (ListUtil.isNullOrEmpty(list)) {
          return ErrorCode.IllegalArument;
      }
      for (MaterialsSafetyStock materialsSafetyStock : list) {
          // 如果修改了安全库存，也需要更新库存上限
          BigDecimal eoqStock = DecimalUtil.getDecimal(materialsSafetyStock.getEoqStock());
          BigDecimal ltdStock = DecimalUtil.getDecimal(materialsSafetyStock.getLtdStock());
          BigDecimal adjustSafetyStock = DecimalUtil.getDecimal(materialsSafetyStock.getAdjustSafetyStock());
          BigDecimal maxLimitStock = adjustSafetyStock.add(eoqStock).add(ltdStock);
          materialsSafetyStock.setUpperLimitStock(maxLimitStock);

          materialsSafetyStock.setUpdateTime(new Date());
          materialsSafetyStock.setUpdateId(userId);
          materialsSafetyStock.setUpdateName(userName);
          materialsSafetyStockMapper.update(materialsSafetyStock);
      }
      return ErrorCode.Success;
  }

  public PageCommonVO list(SearchCommonVO<MaterialsSafetyStock> condition) {
      PageCommonVO result = new PageCommonVO();
      PageHelperPlus.orderBy("create_time desc");
      PageHelperPlus.startPage(condition.getPageNum(), condition.getPageSize());
      condition.getFilters().setMaterialsList(xcmg.device.infra.StringUtil.splitList(condition.getFilters().getMaterialsNos()));
      List<MaterialsSafetyStock> doList = materialsSafetyStockMapper.list(condition.getFilters());
      result.setPageInfo(new PageInfo(doList));
      result.setPageInfoList(doList);
      return result;
  }

  /**
   * 新增零件安全库存信息
   *
   * @param materialsSafetyStock
   * @return
   * @throws Exception
   */
  @Transactional(rollbackFor = Exception.class)
  public Integer saveMaterialsSafetyStock(MaterialsSafetyStock materialsSafetyStock) throws Exception {
    MaterialsSafetyStock safetyStock = materialsSafetyStockMapper.getByMaterialsNo(materialsSafetyStock.getOrgId(), materialsSafetyStock.getMaterialsNo());
    if (null != safetyStock) {
      throw new Exception("零件"+materialsSafetyStock.getMaterialsNo()+"安全库存信息已存在，请勿重复添加");
    }

    Date now = new Date();
    // 查询公司安全库存配置，主要是辨别使用哪个方案做安全库存
    ComSafetyStockInfo comSafetyStockInfo = comSafetyStockInfoMapper.getByOrgId(materialsSafetyStock.getOrgId());
    if(null ==comSafetyStockInfo) {
      throw new Exception("未查询到企业安全库存配置");
    }
    // 生成并获取零件前13个月历史出货数据
    MaterialsSalesData salesData = materialsSalesDataService.gen13MonthSalesData(materialsSafetyStock.getOrgId(),
        materialsSafetyStock.getMaterialsNo(), now, String.valueOf(comSafetyStockInfo.getPlanCode()));
    // 获取安全库存相关数据 TODO 此处逻辑要调整下，改为如果输入了调整后交货期，就不再到sap获取交货期
    SafetyLevelInfo safetyLevelInfo = safetyInventoryStrategy.getSafetyInfo(materialsSafetyStock.getOrgId(), materialsSafetyStock.getMaterialsNo(),
        now, String.valueOf(comSafetyStockInfo.getPlanCode()), materialsSafetyStock.getDeliverySpend());
    PriMaterialsDO materialsDO =priMaterialsMapper.itemByNo(materialsSafetyStock.getMaterialsNo(), materialsSafetyStock.getOrgId());
    if (null == materialsDO) {
      throw new Exception("未查询到零件。orgId:" + materialsSafetyStock.getOrgId() + ", materialsNo:" + materialsSafetyStock.getMaterialsNo());
    }
    materialsSafetyStock.setMaterialsDes(materialsDO.getMaterialsDes());
    return save(materialsSafetyStock, safetyLevelInfo, salesData.getSalesVariance());
  }

  private Integer save(MaterialsSafetyStock materialsSafetyStock, SafetyLevelInfo safetyLevelInfo, BigDecimal variance) {
    materialsSafetyStock = fillSafetyStockInfo(materialsSafetyStock, safetyLevelInfo, variance);
    if (StringUtils.isBlank(materialsSafetyStock.getCreateId()) || StringUtils.isBlank(materialsSafetyStock.getUpdateId())) {
      materialsSafetyStock.setCreateId(SYSTEM_ACTION_CODE);
      materialsSafetyStock.setCreateName(SYSTEM_ACTION_NAME);
      materialsSafetyStock.setCreateTime(new Date());
      materialsSafetyStock.setUpdateId(SYSTEM_ACTION_CODE);
      materialsSafetyStock.setUpdateName(SYSTEM_ACTION_NAME);
      materialsSafetyStock.setUpdateTime(new Date());
    }
    return materialsSafetyStockMapper.insert(materialsSafetyStock);
  }

  @Transactional(rollbackFor = Exception.class)
  public void delete(String materialsNo, String orgId) {
    materialsSafetyStockMapper.delete(materialsNo, orgId);
    materialsSalesDataMapper.delete(materialsNo, orgId);
  }

  @Transactional(rollbackFor = Exception.class)
  public FunctionResult importSafetyStock(MultipartFile file, String orgId, String userId, String userName) throws Exception {
    FunctionResult result = new FunctionResult();
    //Workbook wb = WorkbookFactory.create(in);
    Workbook wb;//同时兼容Excel-2003及Excel-2007
    try {
      InputStream in = file.getInputStream();
      wb = new HSSFWorkbook(in);
    } catch (Exception ex) {
      // 解决read error异常
      // in = new FileInputStream(file);
      InputStream in = file.getInputStream();
      wb = new XSSFWorkbook(in);
    }
    Sheet sheet = wb.getSheetAt(0);
    int totalRow = sheet.getLastRowNum();
    if (totalRow < 2) {
      result.setCode(ErrorCode.IllegalArument);
      result.setValue("导入模板不正确");
      return result;
    }
    int totalCell = sheet.getRow(1).getLastCellNum();
    if (totalCell != 4) {
      result.setCode(ErrorCode.IllegalArument);
      result.setValue("导入模板不正确");
      return result;
    }
    // 第二行第二列，取交货期的单位
    String unit = getCellValue(sheet.getRow(1), 1);
    if (StringUtils.isBlank(unit) || (!DELIVERY_SPEND_UNIT_DAY.equals(unit) && !DELIVERY_SPEND_UNIT_MONTH.equals(unit))) {
      result.setCode(ErrorCode.IllegalArument);
      result.setValue("请正确填写交货期单位");
      return result;
    }
    List<PriMaterialsDO> errorDetailVOList = new ArrayList<>();

    for (int i = 3; i <= totalRow; i++) {
      Row curRow = sheet.getRow(i);
      PriMaterialsDO detailVO = new PriMaterialsDO();
      if (curRow == null) {
        continue;
      }

      String materialNo = getCellValue(curRow, 1);
      String safetyStock = getCellValue(curRow, 2);
      String deliverySpend = getCellValue(curRow, 3);
      if (StringUtils.isBlank(materialNo)) {
        continue;
      }
      if (StringUtils.isBlank(safetyStock) && StringUtils.isBlank(deliverySpend)) {
        continue;
      }
      MaterialsSafetyStock materialsSafetyStock = materialsSafetyStockMapper.getByMaterialsNo(orgId, materialNo);
      BigDecimal realSafetyStock;
      try {
        realSafetyStock = new BigDecimal(safetyStock);
      } catch (NumberFormatException e) {
        detailVO.setErrorRow(i + 1);
        detailVO.setRemark("安全库存格式不正确");
        errorDetailVOList.add(detailVO);
      }

      BigDecimal realSpend = getRealDeliverySpend(deliverySpend, unit);
      if (null != realSpend) {
        PriMaterialsDO materialsDO = priMaterialsMapper.itemByNo(materialNo, orgId);
        if(materialsDO == null){
          detailVO.setErrorRow(i + 1);
          detailVO.setRemark("零件号"+materialNo+"不存在");
          errorDetailVOList.add(detailVO);
          continue;
        }
        materialsDO.setDeliverySpend(realSpend);
        priMaterialsMapper.update(materialsDO);
      }

      realSafetyStock = new BigDecimal(safetyStock);
      if (realSafetyStock.compareTo(BigDecimal.valueOf(999999999)) > 0) {
        detailVO.setErrorRow(i + 1);
        detailVO.setRemark("安全库存过大");
        errorDetailVOList.add(detailVO);
        continue;
      }

      if (null == materialsSafetyStock) {
        MaterialsSafetyStock materialsSafetyStockDO = new MaterialsSafetyStock();
        materialsSafetyStockDO.setOrgId(orgId);
        materialsSafetyStockDO.setMaterialsNo(materialNo);
        materialsSafetyStockDO.setAdjustSafetyStock(realSafetyStock);
        materialsSafetyStockDO.setDeliverySpend(realSpend);
        materialsSafetyStockDO.setCreateId(userId);
        materialsSafetyStockDO.setCreateName(userName);
        materialsSafetyStockDO.setCreateTime(new Date());
        materialsSafetyStockDO.setUpdateId(userId);
        materialsSafetyStockDO.setUpdateName(userName);
        materialsSafetyStockDO.setUpdateTime(new Date());
        try {
          this.saveMaterialsSafetyStock(materialsSafetyStockDO);
         materialsSafetyStockMapper.insert(materialsSafetyStockDO);
        } catch (Exception e) {
          detailVO.setErrorRow(i + 1);
          detailVO.setRemark(e.getMessage());
          errorDetailVOList.add(detailVO);
        }
      } else {
        materialsSafetyStock.setDeliverySpend(realSpend);
        materialsSafetyStock.setAdjustSafetyStock(realSafetyStock);
        List<MaterialsSafetyStock> list = new ArrayList<>();
        list.add(materialsSafetyStock);
        this.update(list, userId, userName);
      }
    }

    if (ListUtil.isNullOrEmpty(errorDetailVOList)) {
      result.setValue(new ArrayList<>());
      result.setCode(ErrorCode.Success);
    } else {
      result.setValue(errorDetailVOList);
      result.setCode(ErrorCode.IllegalArument);
    }
    return result;
  }


  private BigDecimal getRealDeliverySpend(String adjustDeliverySpend, String unit) {
    if (StringUtils.isNotBlank(adjustDeliverySpend)) {
      if (DELIVERY_SPEND_UNIT_DAY.equals(unit)) {
        return new BigDecimal(adjustDeliverySpend).divide(new BigDecimal("30"),2, BigDecimal.ROUND_HALF_UP);
      }
      return new BigDecimal(adjustDeliverySpend).setScale(2, BigDecimal.ROUND_HALF_UP);
    }
    return null;
  }

  private String getCellValue(Row curRow, int i){
    Cell cell = curRow.getCell(i);
    if (null == cell) {
      return StringUtils.EMPTY;
    }
    cell.setCellType(CellType.STRING);
    return StringUtils.trim(cell.getStringCellValue());
  }

  /**
   * 下载价格方案批量导入零件模板
   *
   * @throws Exception
   */
  public void download() throws Exception{
    FileUtil.downLoadFile(response, filePath, SAFETY_STOCK_TEMPLATE_FILE);
  }

}
